/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cl.innovision.helper.utils;

import cl.innovision.vo.Cursor;
import cl.innovision.vo.InParameters;
import cl.innovision.vo.OutParameters;
import cl.innovision.vo.Parameter;
import cl.innovision.vo.Procedure;
import cl.innovision.vo.Procedures;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.jdbc.OracleDatabaseMetaData;

/**
 *
 * @author InnoVision
 */
public class ExtraccionDB {
    
    public ExtraccionDB()
    {
        
    }        
    public ArrayList<String> getSchemas() throws Exception {
        ArrayList<String> v = new ArrayList<String>();
        DatabaseMetaData meta =ConnectionDB.getConexion().getMetaData();
        ResultSet schemas = meta.getSchemas();
        while (schemas.next()) {
            String tableSchema = schemas.getString(1);    // "TABLE_SCHEM"
            v.add(tableSchema);
            //String tableCatalog = schemas.getString(2); //"TABLE_CATALOG"
            System.out.println("tableSchema" + tableSchema);
        }
        return v;
    }
    
    private ArrayList<Cursor>  getResultSets(Procedure p) throws SQLException, Exception
        {
        //conn.setAutoCommit(false);
         Connection conn=  ConnectionDB.getConexion();
         conn.setAutoCommit(false);
         String esquema=ConnectionDB.getInstance().getEsquema();
         
        System.out.println("------------------------------"+p.getName()+"------------------------------");    
        ArrayList<Cursor> cursores = new ArrayList<Cursor>();
       
            StringBuffer sb =new StringBuffer(p.getName()) ;
            sb.append("(  ");
            for(int i=0;i<(p.getInParam().getParam().size());i++)
            {
        
                    sb.append("?, ");
              //  sb.append(", ");
             }
             for(int i=0;i<(p.getOutParam().getParam().size());i++)
            {
               
                    sb.append("?, ");
                // sb.append(", ");
             }
           
            sb.deleteCharAt(sb.length()-2);
            sb.append(" )");
            System.out.println("sp: "+sb.toString());
            esquema=esquema+"."+sb.toString();
            java.sql.CallableStatement cs = conn.prepareCall("{?= call  "+esquema+" }");
            cs.registerOutParameter(1, java.sql.Types.VARCHAR);
            for(int i=0;i<p.getInParam().getParam().size();i++)
            {
                System.out.println("PARAMETRO ENTRADA: "+p.getInParam().getParam().get(i).getName());
                if(p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("number")
                        || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("int")
                        || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("money")
                        || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("decimal")
                        || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("bit"))
                    cs.setInt(i+2, 1);
                else
                    cs.setString(i+2, "");
            }
             for(int o=0;o<p.getOutParam().getParam().size();o++)
            {
                System.out.println("PARAMETRO SALIDA: "+p.getOutParam().getParam().get(o).getName());
               if(p.getOutParam().getParam().get(o).getDbType().equalsIgnoreCase("number")
                       || p.getOutParam().getParam().get(o).getDbType().equalsIgnoreCase("int")
                       || p.getOutParam().getParam().get(o).getDbType().equalsIgnoreCase("money")
                       || p.getOutParam().getParam().get(o).getDbType().equalsIgnoreCase("decimal")
                       || p.getOutParam().getParam().get(o).getDbType().equalsIgnoreCase("bit"))
                    //cs.registerOutParameter(p.getOutParam().getParam().get(o).getName(), java.sql.Types.NUMERIC);
               cs.registerOutParameter(p.getInParam().getParam().size()+o+2, java.sql.Types.NUMERIC);
                 
               else
                     cs.registerOutParameter(p.getInParam().getParam().size()+o+2, java.sql.Types.VARCHAR);
                
            }
             System.out.println("tratandode ejecutar....");
            boolean resultsAvailable=cs.execute();
             System.out.println("Ejecutado!!!!!!");
            int ctCursor=0;
            while(resultsAvailable)
            {
                ResultSet rs=cs.getResultSet();
                ResultSetMetaData md = rs.getMetaData();
                Cursor c=new Cursor(p.getName()+"Cursor"+(ctCursor++));
                System.out.println("********Cursor: "+p.getName()+"Cursor"+(ctCursor++));
                for(int i=1;i<=md.getColumnCount();i++)
                {
                    //String columnDataTypeName= Types.basicTypesDB(md.getColumnTypeName(i));
                    String columnDataTypeName= md.getColumnTypeName(i);
                    System.out.println("TIPO COLUMNA RS: "+columnDataTypeName);
                    System.out.println("COLUMNA RS: "+md.getColumnName(i));
                    System.out.println("PRECISION: "+md.getPrecision(i));
                    System.out.println("ESCALA: "+md.getScale(i));
                    c.addRs(new Parameter(md.getColumnName(i),Types.db2class(columnDataTypeName,md.getPrecision(i),(short)md.getScale(i)),columnDataTypeName,md.getPrecision(i)));
                    
                }
                cursores.add(c);
                resultsAvailable = cs.getMoreResults();
            }
        conn.rollback();
        //conn.close();
        return cursores;
    }
    
    
     public void getResultManual(Procedure p,String[] campos ) throws Exception
        {
        Connection conn=  ConnectionDB.getConexion();
        System.out.println("------------------------------"+p.getName()+"------------------------------");    
        ArrayList<Cursor> cursores = new ArrayList<Cursor>();
        
            StringBuffer sb =new StringBuffer(p.getName()) ;
            sb.append("( ");
            for(int i=0;i<(p.getInParam().getParam().size()+p.getOutParam().getParam().size());i++)
            
                sb.append("?,");
            
            sb.deleteCharAt(sb.length()-1);
            sb.append(" )");
            System.out.println("sp: "+sb.toString());
            java.sql.CallableStatement cs = conn.prepareCall("{call  "+ConnectionDB.getInstance().getEsquema()+"."+sb.toString()+" }");
            
            for(int i=0;i<p.getInParam().getParam().size();i++)
            {
                
                if(p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("number") || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("int") || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("money") || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("decimal") || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("bit"))
                    
                    cs.setInt(i+1, Integer.parseInt(campos[i]));
                else if (p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("DATE"))
                    cs.setDate(i+1, Formateador.string2SQLdate(campos[i]));
                 else
                    cs.setString(i+1, campos[i]);
            }
             for(int o=0;o<p.getOutParam().getParam().size();o++)
            {
                
                if(p.getOutParam().getParam().get(o).getDbType().equalsIgnoreCase("number") || p.getOutParam().getParam().get(o).getDbType().equalsIgnoreCase("int") || p.getOutParam().getParam().get(o).getDbType().equalsIgnoreCase("money") || p.getOutParam().getParam().get(o).getDbType().equalsIgnoreCase("decimal") || p.getOutParam().getParam().get(o).getDbType().equalsIgnoreCase("bit"))
                    cs.registerOutParameter(p.getInParam().getParam().size()+o+1, java.sql.Types.NUMERIC);
                else
                     cs.registerOutParameter(p.getInParam().getParam().size()+o+1, java.sql.Types.VARCHAR);
                
            }
            boolean resultsAvailable=cs.execute();
            int ctCursor=0;
            while(resultsAvailable)
            {
                ResultSet rs=cs.getResultSet();
                ResultSetMetaData md = rs.getMetaData();
                Cursor c=new Cursor(p.getName()+"Cursor"+(ctCursor++));
                System.out.println("********Cursor: "+p.getName()+"Cursor"+(ctCursor++));
                for(int i=1;i<=md.getColumnCount();i++)
                {
                    
                    String columnDataTypeName= Types.basicTypesDB(md.getColumnTypeName(i));
                    System.out.println("COLUMNA RS: "+md.getColumnName(i));
                    System.out.println("PRECISION: "+md.getPrecision(i));
                    System.out.println("ESCALA: "+md.getScale(i));
                    c.addRs(new Parameter(md.getColumnName(i),Types.db2class(columnDataTypeName,md.getPrecision(i),(short)md.getScale(i)),columnDataTypeName,md.getPrecision(i)));
                    
                }
                cursores.add(c);
                resultsAvailable = cs.getMoreResults();
            }
         p.setGenerable(true);
         p.getOutParam().setRs(cursores);
         Procedures.getInstance().updateProcedure(p);
         conn.rollback();
       //  conn.close();
    }
    

    public void getProcedures(String shemaPattern, String procedurePattern) throws Exception  {
            Connection conn=  ConnectionDB.getConexion();
            String esquema=ConnectionDB.getInstance().getEsquema();
            try{
            
            DatabaseMetaData dbMetaData =  conn.getMetaData();
            //ResultSet rsProc = dbMetaData.getProcedures(conn.getCatalog(), "dbo", procedurePattern);
            ResultSet rsProc = dbMetaData.getProcedures(conn.getCatalog(), esquema, procedurePattern);
            //ResultSet rsProc = dbMetaData.getProcedures(conn.getCatalog(), shemaPattern, procedurePattern);
            Procedures.getInstance().removeAll();
            while (rsProc.next()) {
            try {
                String procedureCatalog = rsProc.getString(1);
                String procedureSchema = rsProc.getString(2);
                System.out.println("procedureSchema: " + procedureSchema);
                String procedureName = rsProc.getString(3).replace(";1", "");
                ResultSet rs = dbMetaData.getProcedureColumns(procedureCatalog, procedureSchema, procedureName, null);

                Procedure procedure = null;
                procedure = new Procedure(procedureCatalog, procedureSchema, procedureName);
                Procedures.getInstance().addPrecedures(procedure);
                InParameters inP=new InParameters();
                OutParameters outP=new OutParameters();
                Cursor cursor=null;
                while (rs.next()) {

                      try {
                    String columnName = rs.getString(4).substring(0, rs.getString("COLUMN_NAME").length());                    
                   // String columnName =rs.getString("COLUMN_NAME");
                    columnName=columnName.replaceAll("@", "");
                    short columnType = rs.getShort("COLUMN_TYPE");
                    int columnDataType = rs.getInt("DATA_TYPE");
                    String columnDataTypeName = rs.getString("TYPE_NAME");
                    int columnPrecision = rs.getInt("PRECISION");
                    int columnByteLength = rs.getInt("LENGTH");
                    short columnScale = rs.getShort("SCALE");
                    short columnRadix = rs.getShort("RADIX");
                    short columnNullable = rs.getShort("NULLABLE");
                    String columnRemarks = rs.getString("REMARKS");
                    System.out.println("procedureName: " + procedureName);
                    System.out.println("columnName: " + columnName);
                    System.out.println("columnDataTypeName: " + columnDataTypeName);
                    System.out.println("columnPrecision: " + columnPrecision);
                    System.out.println("columnScale: " + columnScale);
                    System.out.println("columnType: " + columnType);
                    
                                  
                    if(columnDataTypeName.equals("REF CURSOR"))
                    {
                        cursor=new Cursor(columnName);
                        rs.next();
                        rs.next();
                        columnName = rs.getString(4).substring(0, rs.getString("COLUMN_NAME").length());   
                        columnDataTypeName = rs.getString("TYPE_NAME");
                        columnPrecision = rs.getInt("PRECISION");
                        columnScale = rs.getShort("SCALE");
                       /* Parameter p =new Parameter(columnName, Types.db2class(columnDataTypeName, columnPrecision, columnScale),columnDataTypeName,columnPrecision );
                        cursor.addRs(p);*/
                    }
                   
                    if (columnType == DatabaseMetaData.procedureColumnIn) {
                        Parameter p =new Parameter(columnName, Types.db2class(columnDataTypeName, columnPrecision, columnScale),columnDataTypeName,columnPrecision );
                         inP.addParam(p);
                    }
                    if (columnType == DatabaseMetaData.procedureColumnOut && cursor==null) {
                        Parameter p =new Parameter(columnName, Types.db2class(columnDataTypeName, columnPrecision, columnScale),columnDataTypeName,columnPrecision );
                        outP.addParam(p);
                    }
                    
                    if (columnType == DatabaseMetaData.procedureColumnInOut && cursor==null) {
                        Parameter p =new Parameter(columnName, Types.db2class(columnDataTypeName, columnPrecision, columnScale),columnDataTypeName,columnPrecision );
                        outP.addParam(p);
                    }
                     
                     if(cursor!=null)
                     {
                         Parameter p =new Parameter(columnName, Types.db2class(columnDataTypeName, columnPrecision, columnScale),columnDataTypeName,columnPrecision );
                         cursor.addRs(p);
                         outP.addCursor(cursor);
                     }
                     
                     }
                     catch (Exception e)
                {
                    procedure.setGenerable(false);
                    Logger.getLogger(ConnectionDB.class.getName()).log(Level.SEVERE, null, e);
                }
                    //log.debug("columnType: "+tipoColumna(columnType));
                    
                   

                   
                  
                   
                }
                
                procedure.setInParam(inP);
                procedure.setOutParam(outP);  
                try{
                     if (!ConnectionDB.getNombreTipoBD().equalsIgnoreCase("oracle")) 
                        outP.setRs(getResultSets(procedure));
                }
                catch (Exception e)
                {
                    procedure.setGenerable(false);
                    Logger.getLogger(ConnectionDB.class.getName()).log(Level.SEVERE, null, e);
                }
                //procedure=dynamicExecute(conn,procedure);
            } catch (SQLException ex) {
                Logger.getLogger(ConnectionDB.class.getName()).log(Level.SEVERE, null, ex);
            }
           
            }
        }
         catch (SQLException ex){
              Logger.getLogger(ConnectionDB.class.getName()).log(Level.SEVERE, null, ex);
        }
       //conn.close();
        
    }
    
    
    private String tipoColumna(int val)
        {
             switch (val) {
        case DatabaseMetaData.procedureColumnUnknown:
            return "PARAMETER_UNKNOWN";    
        case DatabaseMetaData.procedureColumnIn:
            return "PARAMETER_IN";
        case DatabaseMetaData.procedureColumnInOut:
            return "PARAMETER_INOUT";  
        case DatabaseMetaData.procedureColumnOut:
            return "PARAMETER_OUT";
        case DatabaseMetaData.procedureColumnReturn:
            return "RETURN";
        default:
            return "PARAMETER_UNKNOWN";    
        }
        }
}
